package com.dy.yunying.biz.dao.clickhouse3399.impl;

import com.dy.yunying.api.constant.RetentionKpiEnum;
import com.dy.yunying.api.dto.RetentionDto;
import com.dy.yunying.api.vo.RetentionVo;
import com.pig4cloud.pig.common.core.constant.enums.PlanAttrStatTypeEnum;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.List;
import java.util.Objects;

/**
 * @ClassName RetentionDao
 * @Description todo
 * @Author nieml
 * @Time 2021/6/21 16:05
 * @Version 1.0
 **/
@Component
@Slf4j
public class RetentionDao {

	@Resource(name="clickhouseTemplate")
	private JdbcTemplate clickhouseTemplate;

	public List<RetentionVo> selectRetentionData(RetentionDto req) {
		StringBuilder sql = getAllSql(req);
		log.info("留存数据查询sql:[{}]", sql.toString());

		List<RetentionVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<RetentionVo>(RetentionVo.class));
		return list;
	}


	//获取维度名称
	private StringBuilder getAllSql(RetentionDto req) {
		String queryColumn = req.getQueryColumn();
		StringBuilder sql = getSql(req);
		StringBuilder parentChlNameSql = getParentChlNameSql(req);
		String selectSql = getSelectConditionAndAlias(req, "res");
		if (StringUtils.isBlank(queryColumn)){
			return sql;
		}
		StringBuilder allSql = new StringBuilder();
		allSql.append(" select  ");
		if (StringUtils.isNotBlank(req.getQueryColumn()) && req.getQueryColumn().contains(PlanAttrStatTypeEnum.GAMEID.V())) {
			allSql.append(" t2000.gName gName,");
		}
		allSql.append(selectSql +
				"\tres.cost cost,\n" +
				"\tres.rudecost rudecost,\n" +
				"\tres.usrNameNums usrNameNums,\n" +
				"\tres.retention2 retention2,\n" +
				"\tres.retention3,\n" +
				"\tres.retention4,\n" +
				"\tres.retention5,\n" +
				"\tres.retention6,\n" +
				"\tres.retention7,\n" +
				"\tres.retention8,\n" +
				"\tres.retention9,\n" +
				"\tres.retention10,\n" +
				"\tres.retention11,\n" +
				"\tres.retention12,\n" +
				"\tres.retention13,\n" +
				"\tres.retention14,\n" +
				"\tres.retention15,\n" +
				"\tres.retention16,\n" +
				"\tres.retention17,\n" +
				"\tres.retention18,\n" +
				"\tres.retention19,\n" +
				"\tres.retention20,\n" +
				"\tres.retention21,\n" +
				"\tres.retention22,\n" +
				"\tres.retention23,\n" +
				"\tres.retention24,\n" +
				"\tres.retention25,\n" +
				"\tres.retention26,\n" +
				"\tres.retention27,\n" +
				"\tres.retention28,\n" +
				"\tres.retention29,\n" +
				"\tres.retention30 ");
		if (queryColumn.contains("parentchl")){
			allSql.append(" ,case when a.parentchlName is null or a.parentchlName = '' then '-' else a.parentchlName end parentchlName ");
		}
		if (queryColumn.contains("deptId")){
			allSql.append(" ,case when osd.name is null or osd.name = '' then '-' else osd.name end deptName ");
		}
		if (queryColumn.contains("investor")){
			allSql.append(" ,case when osu.real_name is null or osu.real_name = '' then '-' else osu.real_name end investorName ");
		}
		allSql.append("from (" + sql + " ) res ");
		if (queryColumn.contains("parentchl")){
			allSql.append( " left join" );
			allSql.append(parentChlNameSql);
			allSql.append(" on res.parentchl = a.parentchl ");
		}
		if (queryColumn.contains("deptId")){
			allSql.append( " LEFT JOIN odsmysql_sys_dept osd ON res.deptId = osd.dept_id " );
		}
		if (queryColumn.contains("investor")){
			allSql.append( " LEFT JOIN odsmysql_sys_user osu ON res.investor = osu.user_id " );
		}
		if (StringUtils.isNotBlank(req.getQueryColumn()) && req.getQueryColumn().contains(PlanAttrStatTypeEnum.GAMEID.V())) {
			allSql.append("     LEFT JOIN (");
			allSql.append("     SELECT");
			allSql.append("     t050.id gameid,");
			allSql.append("     t050.gname gName");
			allSql.append("     FROM odsmysql_wan_game t050");
			allSql.append("     ) t2000");
			allSql.append("	ON res.gameid = t2000.gameid");
		}
		return allSql;
	}


	//组装留存的查询sql todo
	private StringBuilder getSql(RetentionDto req) {
		StringBuilder query_sql = new StringBuilder();
		String selectCondition = getSelectCondition(req);
		String groupByCondition = getGroupByCondition(req);
		query_sql.append(" SELECT ");
		//获取查询的维度字段
		query_sql.append(selectCondition);
		query_sql.append(
				"\tSUM(cost) cost,\n" +
				"\tSUM(rudecost) rudecost,\n" +
				"\tSUM(usrnamenums) usrNameNums,\n" +
				"\tSUM(retention2) retention2,\n" +
				"\tSUM(retention3) retention3,\n" +
				"\tSUM(retention4) retention4,\n" +
				"\tSUM(retention5) retention5,\n" +
				"\tSUM(retention6) retention6,\n" +
				"\tSUM(retention7) retention7,\n" +
				"\tSUM(retention8) retention8,\n" +
				"\tSUM(retention9) retention9,\n" +
				"\tSUM(retention10) retention10,\n" +
				"\tSUM(retention11) retention11,\n" +
				"\tSUM(retention12) retention12,\n" +
				"\tSUM(retention13) retention13,\n" +
				"\tSUM(retention14) retention14,\n" +
				"\tSUM(retention15) retention15,\n" +
				"\tSUM(retention16) retention16,\n" +
				"\tSUM(retention17) retention17,\n" +
				"\tSUM(retention18) retention18,\n" +
				"\tSUM(retention19) retention19,\n" +
				"\tSUM(retention20) retention20,\n" +
				"\tSUM(retention21) retention21,\n" +
				"\tSUM(retention22) retention22,\n" +
				"\tSUM(retention23) retention23,\n" +
				"\tSUM(retention24) retention24,\n" +
				"\tSUM(retention25) retention25,\n" +
				"\tSUM(retention26) retention26,\n" +
				"\tSUM(retention27) retention27,\n" +
				"\tSUM(retention28) retention28,\n" +
				"\tSUM(retention29) retention29,\n" +
				"\tSUM(retention30) retention30\n" +
				"FROM ( ");
		query_sql.append(" \tSELECT\n" +
				"adid," +
				selectCondition +
				"\t\tcase when cost is null then 0 else cost end cost,\n" +
				"\t\tcase when rudecost is null then 0 else rudecost end rudecost,\n" +
				"\t\tcase when usrnamenums is null then 0 else usrnamenums end usrnamenums,\n" +
				"\t\tretention2,\n" +
				"\t\tretention3,\n" +
				"\t\tretention4,\n" +
				"\t\tretention5,\n" +
				"\t\tretention6,\n" +
				"\t\tretention7,\n" +
				"\t\tretention8,\n" +
				"\t\tretention9,\n" +
				"\t\tretention10,\n" +
				"\t\tretention11,\n" +
				"\t\tretention12,\n" +
				"\t\tretention13,\n" +
				"\t\tretention14,\n" +
				"\t\tretention15,\n" +
				"\t\tretention16,\n" +
				"\t\tretention17,\n" +
				"\t\tretention18,\n" +
				"\t\tretention19,\n" +
				"\t\tretention20,\n" +
				"\t\tretention21,\n" +
				"\t\tretention22,\n" +
				"\t\tretention23,\n" +
				"\t\tretention24,\n" +
				"\t\tretention25,\n" +
				"\t\tretention26,\n" +
				"\t\tretention27,\n" +
				"\t\tretention28,\n" +
				"\t\tretention29,\n" +
				"\t\tretention30 ");
		query_sql.append(" from ");
		query_sql.append(getRetentionSql(req));
		query_sql.append(" FULL JOIN  ");
		query_sql.append(getAdDataSql(req));
		//获取留存数据 full join 广告数据的关联字段 todo
		query_sql.append(" USING ( adid,");
		query_sql.append(groupByCondition);
		query_sql.append(" ) ");
		query_sql.append(" ) t1000 ");
		//获取分组维度字段 todo
		query_sql.append(" group by ");
		query_sql.append(groupByCondition);
		query_sql.append(" order by period desc ");
		return query_sql;
	}


	/*
	* 拼装留存sql
	* */
	private StringBuilder getRetentionSql(RetentionDto req){
		StringBuilder query_sql = new StringBuilder();
		String period = req.getPeriod();
		String retentionKpi = req.getRetentionKpi();
		String selectCondition = getSelectCondition(req);
		String groupByCondition = getGroupByCondition(req);
		String newDeviceRegSql = getNewDeviceRegSql(req);
		String newDevicePaySql = getNewDevicePaySql(req);
		String newDeviceRetentionSql = getNewDeviceRetentionSql(req,retentionKpi);
		query_sql.append(" ( ");
		query_sql.append(
				"\t\t\t--计算留存\n" +
				"\t\t\tSELECT\n" +
				" adid, " +
				selectCondition +
				"\t\t\t\tusrnamenums,\n" +
				"\t\t\t\tretention2,\n" +
				"\t\t\t\tretention3,\n" +
				"\t\t\t\tretention4,\n" +
				"\t\t\t\tretention5,\n" +
				"\t\t\t\tretention6,\n" +
				"\t\t\t\tretention7,\n" +
				"\t\t\t\tretention8,\n" +
				"\t\t\t\tretention9,\n" +
				"\t\t\t\tretention10,\n" +
				"\t\t\t\tretention11,\n" +
				"\t\t\t\tretention12,\n" +
				"\t\t\t\tretention13,\n" +
				"\t\t\t\tretention14,\n" +
				"\t\t\t\tretention15,\n" +
				"\t\t\t\tretention16,\n" +
				"\t\t\t\tretention17,\n" +
				"\t\t\t\tretention18,\n" +
				"\t\t\t\tretention19,\n" +
				"\t\t\t\tretention20,\n" +
				"\t\t\t\tretention21,\n" +
				"\t\t\t\tretention22,\n" +
				"\t\t\t\tretention23,\n" +
				"\t\t\t\tretention24,\n" +
				"\t\t\t\tretention25,\n" +
				"\t\t\t\tretention26,\n" +
				"\t\t\t\tretention27,\n" +
				"\t\t\t\tretention28,\n" +
				"\t\t\t\tretention29,\n" +
				"\t\t\t\tretention30\n" +
				"FROM ");
		//t001:新增设备注册设备数
		if (RetentionKpiEnum.PAY.getType().equals(retentionKpi)){
			query_sql.append(newDevicePaySql);
		}else {
			query_sql.append(newDeviceRegSql);
		}
		query_sql.append(" FULL JOIN ");
		//t002:新增设备留存
		query_sql.append(newDeviceRetentionSql);
		query_sql.append(" USING ( ");
		query_sql.append("adid,");
		query_sql.append(groupByCondition);
		query_sql.append(")");
		query_sql.append(" ) t100 ");
		return query_sql;
	}


	private String getNewDeviceRegSql(RetentionDto req) {
		StringBuilder query_sql = new StringBuilder();
		String period = req.getPeriod();
		Long sTime = req.getSTime();
		Long eTime = req.getETime();
		String selectCondition = getSelectCondition(req);
		String groupByCondition = getGroupByCondition(req);
		StringBuilder authSql = getAuthSql(req);
		String whereCondition = getWhereCondition(req, "");
		query_sql.append(" ( " +
				"\t\t\t\t-- 新增设备  累计注册账号的设备数\n" +
				"\t\t\t\tSELECT " +
				" adid, " +
				selectCondition +
				"\t\t\t\t\tCOUNT(DISTINCT ud.dr_kid) usrnamenums\n" +
				"\t\t\t\tfrom (\n" +
				"\t\t\t\t    -- 新增设备及其广告归因\n" +
				"\t\t\t\t\tSELECT a.day day," +
				"\t\t\t\t parseDateTimeBestEffort(toString(day)) dayDate, \n" +
				"\t\t\t\t toString(toYear(dayDate)) yearStr, \n" +
				"\t\t\t\t toMonth(dayDate) monthInt, \n" +
				"\t\t\t\t toYearWeek(dayDate, 3) weekInt, \n" +
				"\t\t\t\t concat(yearStr, '-', IF(10>monthInt, '0', ''), toString(monthInt), '月') AS monthStr, \n" +
				"\t\t\t\t concat(toString(weekInt), '周') AS weekStr, \n" +
				period + " as period, " +
				" \t\t\t\t\t\ta.pgid pgid,\n" +
				"\t\t\t\t\t\ta.gameid gameid,\n" +
				"\t\t\t\t\t\ta.os os,\n" +
				"\t\t\t\t\t\ta.parentchl parentchl,\n" +
				"\t\t\t\t\t\ta.chl chl,\n" +
				"\t\t\t\t\t\ta.appchl appchl,\n" +
				"\t\t\t\t\t\twpc.manage investor,\n" +
				"\t\t\t\t\t\tosu.dept_id deptId,\n" +
				"\t\t\t\t\t\tIFNULL(osu.dept_group_id,0) userGroupId, \n"+
				"\t\t\t\t\t\tIFNULL(osdg.name,'') userGroupName, \n"+
				"\t\t\t\t\t\t(case\n" +
				"\t\t\t\t\t\t\twhen ad.adid is not null THEN ad.adid\n" +
				"\t\t\t\t\t\t\telse ''\n" +
				"\t\t\t\t\tend) as adid,\n" +
				"\t\t\t\t\t\t(case\n" +
				"\t\t\t\t\t\t\twhen ad.adname is not null THEN ad.adname\n" +
				"\t\t\t\t\t\t\telse ''\n" +
				"\t\t\t\t\tend) as adidName,\n" +
				"\t\t\t\t\t\t(case\n" +
				"\t\t\t\t\t\t\twhen ad.adaccount is not null THEN ad.adaccount\n" +
				"\t\t\t\t\t\t\telse ''\n" +
				"\t\t\t\t\tend) as adAccount, " +
				"\t\t\t\t\t\ta.uuid uuid,a.kid kid,a.receivetime receivetime,ad.advert_id advert_id\n" +
				"\t\t\t\t\tfrom thirty_game_device_reg a\n" +
				"\t\t\t\t\tLEFT JOIN v_thirty_ad_device ad\n" +
				"\t\t\t\t\ton a.uuid = ad.uuid\n" +
				"\t\t\t\t\tand a.pgid = ad.pgid\n" +
				"\t\t\t\t\tand a.day = ad.`day`\n" +
				"\t\t\t\t\tLEFT JOIN odsmysql_wan_promotion_channel_v3 wpc ON\n" +
				"\t\t\t\t\t\twpc.isdelete = 0\n" +
				"\t\t\t\t\t\tAND wpc.parent_code = a.parentchl\n" +
				"\t\t\t\t\t\tAND wpc.chncode = a.chl\n" +
				"\t\t\t\t\tLEFT JOIN odsmysql_sys_user osu ON\n" +
				"\t\t\t\t\t\twpc.manage = osu.user_id\n" +
				"\t\t\t\t\t left join odsmysql_sys_dept_group osdg\n" +
				"\t\t\t\t\t\t on osu.dept_group_id = osdg.id\n" +
				"\t\t\t\t\tLEFT JOIN v_odsmysql_adid vad ON\n" +
				"\t\t\t\t\t\tad.adid = vad.adid\n" +
				"\t\t\t\t\t\tAND ad.ctype = vad.ctype\n" +
				"\t\t\t\t\twhere 1=1\n" +
				"\t\t\t\t\tand a.day >= " + sTime +
				"\t\t\t\t\tand a.day <= " + eTime +
				"\t\t\t\t) reg\n" +
				"\t\t\t\tLEFT JOIN (\n" +
				"\t\t\t\t\t-- 新增设备上注册的账号\n" +
				"\t\t\t\t\tselect\n" +
				"\t\t\t\t\t\tur.kid ur_kid,\n" +
				"\t\t\t\t\t\targMax(dr.kid, dr.receivetime) dr_kid,\n" +
				"\t\t\t--\t\t\targMax(ur.day, dr.receivetime) day,\n" +
				"\t\t\t--\t\t\targMax(ur.uuid, dr.receivetime) uuid,\n" +
				"\t\t\t\t\t\targMax(ur.usrname, dr.receivetime) usrname\n" +
				"\t\t\t--\t\t\targMax(ur.gameid, dr.receivetime) gameid,\n" +
				"\t\t\t--\t\t\targMax(dr.parentchl, dr.receivetime) parentchl,\n" +
				"\t\t\t--\t\t\targMax(dr.chl, dr.receivetime) chl,\n" +
				"\t\t\t--\t\t\targMax(dr.appchl, dr.receivetime) appchl\n" +
				"\t\t\t\t\tfrom v_game_account_reg ur\n" +
				"\t\t\t\t\tINNER join odsmysql_wan_game g\n" +
				"\t\t\t\t\ton ur.gameid = g.id\n" +
				"\t\t\t\t\tINNER join thirty_game_device_reg  dr\n" +
				"\t\t\t\t\ton ur.uuid = dr.uuid\n" +
				"\t\t\t\t\tand g.pgid = dr.pgid\n" +
				"\t\t\t\t\t--and ur.day =dr.`day`\n" +
				"\t\t\t\t\twhere 1=1\n" +
				"\t\t\t\t\tand  ur.receivetimes >= dr.receivetime\n" +
				"\t\t\t\t\tand dr.day >= " + sTime +
				"\t\t\t\t\tand dr.day <= " + + eTime +
				"\t\t\t\t\tgroup by ur.kid\n" +
				"\t\t\t\t) ud\n" +
				"\t\t\t\ton reg.kid = ud.dr_kid\n" +
				"\t\t\t\twhere 1=1\n" +
				"-- 筛选条件\n" +
				whereCondition +
				"-- 渠道权限\n" +
				authSql +
				"\t\t\t\tGROUP by " +
				"adid," +
				groupByCondition +
				" having usrnamenums is not null " +
				" ) t001 "
		);
		return query_sql.toString();
	}


	private String getNewDevicePaySql(RetentionDto req) {
		StringBuilder query_sql = new StringBuilder();
		String period = req.getPeriod();
		Long sTime = req.getSTime();
		Long eTime = req.getETime();
		String selectCondition = getSelectCondition(req);
		String groupByCondition = getGroupByCondition(req);
		StringBuilder authSql = getAuthSql(req);
		String whereCondition = getWhereCondition(req, "");
		query_sql.append(" ( " +
				"\t\t\t\t-- 新增设备当日付费设备数\n" +
				"\t\t\t\tSELECT " +
				" adid, " +
				selectCondition +
				"\t\t\t\t\tCOUNT(DISTINCT reg.kid) usrnamenums\n" +
				"\t\t\t\tfrom (\n" +
				"\t\t\t\t    -- 新增设备及其广告归因\n" +
				"\t\t\t\t\tSELECT a.day day," +
				"\t\t\t\t parseDateTimeBestEffort(toString(day)) dayDate, \n" +
				"\t\t\t\t toString(toYear(dayDate)) yearStr, \n" +
				"\t\t\t\t toMonth(dayDate) monthInt, \n" +
				"\t\t\t\t toYearWeek(dayDate, 3) weekInt, \n" +
				"\t\t\t\t concat(yearStr, '-', IF(10>monthInt, '0', ''), toString(monthInt), '月') AS monthStr, \n" +
				"\t\t\t\t concat(toString(weekInt), '周') AS weekStr, \n" +
				period + " as period, " +
				" \t\t\t\t\t\ta.pgid pgid,\n" +
				"\t\t\t\t\t\ta.gameid gameid,\n" +
				"\t\t\t\t\t\ta.os os,\n" +
				"\t\t\t\t\t\ta.parentchl parentchl,\n" +
				"\t\t\t\t\t\ta.chl chl,\n" +
				"\t\t\t\t\t\ta.appchl appchl,\n" +
				"\t\t\t\t\t\twpc.manage investor,\n" +
				"\t\t\t\t\t\tosu.dept_id deptId,\n" +
				"\t\t\t\t\t\tIFNULL(osu.dept_group_id,0) userGroupId, \n"+
				"\t\t\t\t\t\tIFNULL(osdg.name,'') userGroupName, \n"+
				"\t\t\t\t\t\t(case\n" +
				"\t\t\t\t\t\t\twhen ad.adid is not null THEN ad.adid\n" +
				"\t\t\t\t\t\t\telse ''\n" +
				"\t\t\t\t\tend) as adid,\n" +
				"\t\t\t\t\t\t(case\n" +
				"\t\t\t\t\t\t\twhen ad.adname is not null THEN ad.adname\n" +
				"\t\t\t\t\t\t\telse ''\n" +
				"\t\t\t\t\tend) as adidName,\n" +
				"\t\t\t\t\t\t(case\n" +
				"\t\t\t\t\t\t\twhen ad.adaccount is not null THEN ad.adaccount\n" +
				"\t\t\t\t\t\t\telse ''\n" +
				"\t\t\t\t\tend) as adAccount, " +
				"\t\t\t\t\t\ta.uuid uuid,a.kid kid,a.receivetime receivetime,ad.advert_id advert_id\n" +
				"\t\t\t\t\tfrom thirty_game_device_reg a\n" +
				"-- 过滤出当日付费设备\n" +
				" INNER join (select uuid,day from original_user_recharge where 1=1 group by uuid,day) our on a.uuid = our.uuid and a.day = our.day " +
				"\t\t\t\t\tLEFT JOIN v_thirty_ad_device ad\n" +
				"\t\t\t\t\ton a.uuid = ad.uuid\n" +
				"\t\t\t\t\tand a.pgid = ad.pgid\n" +
				"\t\t\t\t\tand a.day = ad.`day`\n" +
				"\t\t\t\t\tLEFT JOIN odsmysql_wan_promotion_channel_v3 wpc ON\n" +
				"\t\t\t\t\t\twpc.isdelete = 0\n" +
				"\t\t\t\t\t\tAND wpc.parent_code = a.parentchl\n" +
				"\t\t\t\t\t\tAND wpc.chncode = a.chl\n" +
				"\t\t\t\t\tLEFT JOIN odsmysql_sys_user osu ON\n" +
				"\t\t\t\t\t\twpc.manage = osu.user_id\n" +
				"\t\t\t\t\t left join odsmysql_sys_dept_group osdg\n" +
				"\t\t\t\t\t\t on osu.dept_group_id = osdg.id\n" +
				"\t\t\t\t\tLEFT JOIN v_odsmysql_adid vad ON\n" +
				"\t\t\t\t\t\tad.adid = vad.adid\n" +
				"\t\t\t\t\t\tAND ad.ctype = vad.ctype\n" +
				"\t\t\t\t\twhere 1=1\n" +
				"\t\t\t\t\tand a.day >= " + sTime +
				"\t\t\t\t\tand a.day <= " + eTime +
				"\t\t\t\t) reg\n" +
				"\t\t\t\twhere 1=1\n" +
				"-- 筛选条件\n" +
				whereCondition +
				"-- 渠道权限\n" +
				authSql +
				"\t\t\t\tGROUP by " +
				"adid," +
				groupByCondition +
				" ) t001 "
		);
		return query_sql.toString();
	}


	private String getNewDeviceRetentionSql(RetentionDto req, String retentionKpi) {
		StringBuilder query_sql = new StringBuilder();
		String period = req.getPeriod();
		Long sTime = req.getSTime();
		Long eTime = req.getETime();
		String selectCondition = getSelectCondition(req);
		String groupByCondition = getGroupByCondition(req);
		StringBuilder authSql = getAuthSql(req);
		String whereCondition = getWhereCondition(req, "");
		String paySql = "";
		if (RetentionKpiEnum.PAY.getType().equals(retentionKpi)){
			paySql = getPaySql(req);
		}
		query_sql.append(
				" ( " +
				"\t\t\t    -- 留存（有登录）\n" +
				"\t\t\t\tSELECT " +
				"adid," +
				selectCondition +
				"\t\t\t\t\tMAX(niff) dayDiff,\n" +
				"\t\t\t\t\t(case when dayDiff < 1 then NULL else IFNULL(uniqExact(activeuuid2), 0) end) retention2,\n" +
				"\t\t\t\t\t(case when dayDiff < 2 then NULL else IFNULL(uniqExact(activeuuid3), 0) end) retention3,\n" +
				"\t\t\t\t\t(case when dayDiff < 3 then NULL else IFNULL(uniqExact(activeuuid4), 0) end) retention4,\n" +
				"\t\t\t\t\t(case when dayDiff < 4 then NULL else IFNULL(uniqExact(activeuuid5), 0) end) retention5,\n" +
				"\t\t\t\t\t(case when dayDiff < 5 then NULL else IFNULL(uniqExact(activeuuid6), 0) end) retention6,\n" +
				"\t\t\t\t\t(case when dayDiff < 6 then NULL else IFNULL(uniqExact(activeuuid7), 0) end) retention7,\n" +
				"\t\t\t\t\t(case when dayDiff < 7 then NULL else IFNULL(uniqExact(activeuuid8), 0) end) retention8,\n" +
				"\t\t\t\t\t(case when dayDiff < 8 then NULL else IFNULL(uniqExact(activeuuid9), 0) end) retention9,\n" +
				"\t\t\t\t\t(case when dayDiff < 9 then NULL else IFNULL(uniqExact(activeuuid10), 0) end) retention10,\n" +
				"\t\t\t\t\t(case when dayDiff < 10 then NULL else IFNULL(uniqExact(activeuuid11), 0) end) retention11,\n" +
				"\t\t\t\t\t(case when dayDiff < 11 then NULL else IFNULL(uniqExact(activeuuid12), 0) end) retention12,\n" +
				"\t\t\t\t\t(case when dayDiff < 12 then NULL else IFNULL(uniqExact(activeuuid13), 0) end) retention13,\n" +
				"\t\t\t\t\t(case when dayDiff < 13 then NULL else IFNULL(uniqExact(activeuuid14), 0) end) retention14,\n" +
				"\t\t\t\t\t(case when dayDiff < 14 then NULL else IFNULL(uniqExact(activeuuid15), 0) end) retention15,\n" +
				"\t\t\t\t\t(case when dayDiff < 15 then NULL else IFNULL(uniqExact(activeuuid16), 0) end) retention16,\n" +
				"\t\t\t\t\t(case when dayDiff < 16 then NULL else IFNULL(uniqExact(activeuuid17), 0) end) retention17,\n" +
				"\t\t\t\t\t(case when dayDiff < 17 then NULL else IFNULL(uniqExact(activeuuid18), 0) end) retention18,\n" +
				"\t\t\t\t\t(case when dayDiff < 18 then NULL else IFNULL(uniqExact(activeuuid19), 0) end) retention19,\n" +
				"\t\t\t\t\t(case when dayDiff < 19 then NULL else IFNULL(uniqExact(activeuuid20), 0) end) retention20,\n" +
				"\t\t\t\t\t(case when dayDiff < 20 then NULL else IFNULL(uniqExact(activeuuid21), 0) end) retention21,\n" +
				"\t\t\t\t\t(case when dayDiff < 21 then NULL else IFNULL(uniqExact(activeuuid22), 0) end) retention22,\n" +
				"\t\t\t\t\t(case when dayDiff < 22 then NULL else IFNULL(uniqExact(activeuuid23), 0) end) retention23,\n" +
				"\t\t\t\t\t(case when dayDiff < 23 then NULL else IFNULL(uniqExact(activeuuid24), 0) end) retention24,\n" +
				"\t\t\t\t\t(case when dayDiff < 24 then NULL else IFNULL(uniqExact(activeuuid25), 0) end) retention25,\n" +
				"\t\t\t\t\t(case when dayDiff < 25 then NULL else IFNULL(uniqExact(activeuuid26), 0) end) retention26,\n" +
				"\t\t\t\t\t(case when dayDiff < 26 then NULL else IFNULL(uniqExact(activeuuid27), 0) end) retention27,\n" +
				"\t\t\t\t\t(case when dayDiff < 27 then NULL else IFNULL(uniqExact(activeuuid28), 0) end) retention28,\n" +
				"\t\t\t\t\t(case when dayDiff < 28 then NULL else IFNULL(uniqExact(activeuuid29), 0) end) retention29,\n" +
				"\t\t\t\t\t(case when dayDiff < 29 then NULL else IFNULL(uniqExact(activeuuid30), 0) end) retention30\n" +
				"\t\t\t\tfrom (\n" +
				"\t\t\t\t\tselect\n" +
				"\t\t\t\t\t\targMax(reg.day, reg.receivetime) regDay,\n" +
				"\t\t\t\t\t\targMax(ul.day, reg.receivetime) activeDay,\n" +
				"\t\t\t\t\t\tregDay AS day,\n" +
				"\t\t\t\t parseDateTimeBestEffort(toString(day)) dayDate, \n" +
				"\t\t\t\t toString(toYear(dayDate)) yearStr, \n" +
				"\t\t\t\t toMonth(dayDate) monthInt, \n" +
				"\t\t\t\t toYearWeek(dayDate, 3) weekInt, \n" +
				"\t\t\t\t concat(yearStr, '-', IF(10>monthInt, '0', ''), toString(monthInt), '月') AS monthStr, \n" +
				"\t\t\t\t concat(toString(weekInt), '周') AS weekStr, \n" +
				period + " as period, " +
				"\t\t\t\t\t\targMax(reg.pgid, reg.receivetime) pgid,\n" +
				"\t\t\t\t\t\targMax(reg.gameid, reg.receivetime) AS gameid,\n" +
				"\t\t\t\t\t\targMax(reg.os, reg.receivetime) AS os,\n" +
				"\t\t\t\t\t\targMax(reg.parentchl, reg.receivetime) parentchl,\n" +
				"\t\t\t\t\t\targMax(reg.chl, reg.receivetime) chl,\n" +
				"\t\t\t\t\t\targMax(reg.appchl, reg.receivetime) appchl,\n" +
				"\t\t\t\t\t\targMax(wpc.manage,reg.receivetime) investor,\n" +
				"\t\t\t\t\t\targMax(osu.dept_id, reg.receivetime) deptId,\n" +
				"\t\t\t\t\t\tIFNULL(argMax(osu.dept_group_id,reg.receivetime),0) userGroupId, \n"+
				"\t\t\t\t\t\tIFNULL(argMax(osdg.name,reg.receivetime),'') userGroupName, \n"+
				"\t\t\t\t\t\targMax(ad.adid, reg.receivetime) adidTmp,\n" +
				"\t\t\t\t\t\targMax(ad.adname,reg.receivetime) adidName,\n" +
				"\t\t\t\t\t\targMax(ad.adaccount, reg.receivetime) adAccount,\n" +
				"\t\t\t\t\t\t(case when adidTmp is not null THEN adidTmp else '' end) as adid,\n" +
				"\t\t\t\t\t\tul.kid ulkid,\n" +
				"\t\t\t\t\t\targMax(reg.kid, reg.receivetime) regkid,\n" +
				"\t\t\t\t\t\tdateDiff('day', parseDateTimeBestEffort(toString(regDay)),  today() ) as niff, --距离今天多少天\n" +
				"\t\t\t\t\t\tdateDiff('day', parseDateTimeBestEffort(toString(regDay)), parseDateTimeBestEffort(toString(activeDay))) as diff,\n" +
				"\t\t\t\t\t\targMax(ul.uuid, reg.receivetime) AS uuid,\n" +
				"\t\t\t\t\t\t(case when diff = 1 then uuid else null  end) activeuuid2,\n" +
				"\t\t\t\t\t\t(case when diff = 2 then uuid else null  end) activeuuid3,\n" +
				"\t\t\t\t\t\t(case when diff = 3 then uuid else null  end) activeuuid4,\n" +
				"\t\t\t\t\t\t(case when diff = 4 then uuid else null  end) activeuuid5,\n" +
				"\t\t\t\t\t\t(case when diff = 5 then uuid else null  end) activeuuid6,\n" +
				"\t\t\t\t\t\t(case when diff = 6 then uuid else null  end) activeuuid7,\n" +
				"\t\t\t\t\t\t(case when diff = 7 then uuid else null  end) activeuuid8,\n" +
				"\t\t\t\t\t\t(case when diff = 8 then uuid else null  end) activeuuid9,\n" +
				"\t\t\t\t\t\t(case when diff = 9 then uuid else null  end) activeuuid10,\n" +
				"\t\t\t\t\t\t(case when diff = 10 then uuid else null  end) activeuuid11,\n" +
				"\t\t\t\t\t\t(case when diff = 11 then uuid else null  end) activeuuid12,\n" +
				"\t\t\t\t\t\t(case when diff = 12 then uuid else null  end) activeuuid13,\n" +
				"\t\t\t\t\t\t(case when diff = 13 then uuid else null  end) activeuuid14,\n" +
				"\t\t\t\t\t\t(case when diff = 14 then uuid else null  end) activeuuid15,\n" +
				"\t\t\t\t\t\t(case when diff = 15 then uuid else null  end) activeuuid16,\n" +
				"\t\t\t\t\t\t(case when diff = 16 then uuid else null  end) activeuuid17,\n" +
				"\t\t\t\t\t\t(case when diff = 17 then uuid else null  end) activeuuid18,\n" +
				"\t\t\t\t\t\t(case when diff = 18 then uuid else null  end) activeuuid19,\n" +
				"\t\t\t\t\t\t(case when diff = 19 then uuid else null  end) activeuuid20,\n" +
				"\t\t\t\t\t\t(case when diff = 20 then uuid else null  end) activeuuid21,\n" +
				"\t\t\t\t\t\t(case when diff = 21 then uuid else null  end) activeuuid22,\n" +
				"\t\t\t\t\t\t(case when diff = 22 then uuid else null  end) activeuuid23,\n" +
				"\t\t\t\t\t\t(case when diff = 23 then uuid else null  end) activeuuid24,\n" +
				"\t\t\t\t\t\t(case when diff = 24 then uuid else null  end) activeuuid25,\n" +
				"\t\t\t\t\t\t(case when diff = 25 then uuid else null  end) activeuuid26,\n" +
				"\t\t\t\t\t\t(case when diff = 26 then uuid else null  end) activeuuid27,\n" +
				"\t\t\t\t\t\t(case when diff = 27 then uuid else null  end) activeuuid28,\n" +
				"\t\t\t\t\t\t(case when diff = 28 then uuid else null  end) activeuuid29,\n" +
				"\t\t\t\t\t\t(case when diff = 29 then uuid else null  end) activeuuid30,\n" +
				"\t\t\t\t\t\targMax(ad.advert_id, reg.receivetime) advert_id\n" +
				"\t\t\t\t\tfrom user_login ul\n" +
				"\t\t\t\t\tINNER join odsmysql_wan_game g\n" +
				"\t\t\t\t\ton ul.gameid = g.id\n" +
				"\t\t\t\t\tASOF INNER join thirty_game_device_reg reg\n" +
				"\t\t\t\t\ton ul.uuid = reg.uuid\n" +
				"\t\t\t\t\tand ul.receivetime >= reg.receivetime\n" +
				"\t\t\t\t\tand g.pgid = reg.pgid\n" +
				paySql +
				"\t\t\t\t\tLEFT JOIN v_thirty_ad_device ad\n" +
				"\t\t\t\t\ton reg.uuid = ad.uuid\n" +
				"\t\t\t\t\tand reg.pgid = ad.pgid\n" +
				"\t\t\t\t\tand reg.`day` = ad.`day`\n" +
				"\t\t\t\t\tLEFT JOIN odsmysql_wan_promotion_channel_v3 wpc ON\n" +
				"\t\t\t\t\t\twpc.isdelete = 0\n" +
				"\t\t\t\t\t\tAND wpc.parent_code = reg.parentchl\n" +
				"\t\t\t\t\t\tAND wpc.chncode = reg.chl\n" +
				"\t\t\t\t\tLEFT JOIN odsmysql_sys_user osu ON\n" +
				"\t\t\t\t\t\twpc.manage = osu.user_id\n" +
				"\t\t\t\t\t left join odsmysql_sys_dept_group osdg\n" +
				"\t\t\t\t\t\t on osu.dept_group_id = osdg.id\n" +
				"\t\t\t\t\tLEFT JOIN v_odsmysql_adid vad ON\n" +
				"\t\t\t\t\t\tad.adid = vad.adid\n" +
				"\t\t\t\t\t\tAND ad.ctype = vad.ctype\n" +
				"\t\t\t\t\twhere 1=1\n" +
				"\t\t\t\t\tand ul.`day`>= " + sTime +
				"\t\t\t\t\tand reg.day>= " + sTime +
				"\t\t\t\t\tand reg.day<= " + eTime +
				"\t\t\t\t\tGROUP BY ul.kid\n" +
				"\t\t\t\t) a\n" +
				" where 1=1 \n" +
				"-- 筛选条件\n" +
				whereCondition +
				"-- 渠道权限\n" +
				authSql +
				"\t\t\t\tGROUP BY " +
				"adid," +
				groupByCondition +
				" ) t002 "
		);
		return query_sql.toString();
	}


	private String getPaySql(RetentionDto req) {
		StringBuilder query_sql = new StringBuilder();
		query_sql.append(" INNER join (select uuid,day from original_user_recharge where 1=1 group by uuid,day) our on reg.uuid = our.uuid and reg.day = our.day ");
		return query_sql.toString();
	}


	private StringBuilder getAdDataSql(RetentionDto req){
		StringBuilder query_sql = new StringBuilder();
		String period = req.getPeriod();
		Long sTime = req.getSTime();
		Long eTime = req.getETime();
		String selectCondition = getSelectCondition(req);
		String groupByCondition = getGroupByCondition(req);
		StringBuilder adAuthSql = getAdAuthSql(req);
		String whereCondition = getWhereCondition(req, "");
		query_sql.append(" ( ");
		query_sql.append(" \n" +
				"\t\t\t-- 计划广告数据\n" +
				"\t\t\tSELECT " +
				"adid," +
				selectCondition +
				"\t\t\t\tSUM(cost) cost,\n" +
				"\t\t\t\tSUM(rudecost) rudecost\n" +
				"\t\t\tfrom (\n" +
				"\t\t\t\tSELECT\n" +
				"\t\t\t\t\tb.date day,\n" +
				"\t\t\t\t parseDateTimeBestEffort(toString(day)) dayDate, \n" +
				"\t\t\t\t toString(toYear(dayDate)) yearStr, \n" +
				"\t\t\t\t toMonth(dayDate) monthInt, \n" +
				"\t\t\t\t toYearWeek(dayDate, 3) weekInt, \n" +
				"\t\t\t\t concat(yearStr, '-', IF(10>monthInt, '0', ''), toString(monthInt), '月') AS monthStr, \n" +
				"\t\t\t\t concat(toString(weekInt), '周') AS weekStr, \n" +
				period + " as period, " +
				"\t\t\t\t\tIFNULL(g.pgid, 0) pgid,\n" +
				"\t\t\t\t\tIFNULL(ap.gameid, 0) gameid,\n" +
				"\t\t\t\t\tIFNULL(g.os, 3) os,\n" +
				"\t\t\t\t\t(case when ap.adid is not null and ap.adid !='' then ap.parentchl else '-' end) parentchl,\n" +
				"\t\t\t\t\t(case when ap.adid is not null and ap.adid !='' then ap.chl else '-' end) chl,\n" +
				"\t\t\t\t\t(case when ap.adid is not null and ap.adid !='' then ap.appchl else '-' end) appchl,\n" +
				" \t\t\t\t\tosu.user_id investor,\n" +
				"\t\t\t\t\tosu.dept_id deptId,\n" +
				"\t\t\t\t\tIFNULL(osu.dept_group_id,0) userGroupId, \n"+
				"\t\t\t\t\tIFNULL(osdg.name,'') userGroupName, \n"+
				"\t\t\t\t\tb.ad_id adid,\n" +
				"\t\t\t\t\tb.ad_name adidName,\n" +
				"\t\t\t\t\tb.ad_account adAccount,\n " +
				"\t\t\t\t\tb.adconvert adconvert,\n" +
				"\t\t\t\t\tb.adshow adshow,\n" +
				"\t\t\t\t\tb.click click,\n" +
				"\t\t\t\t\tb.rudecost rudecost,\n" +
				"\t\t\t\t\tb.cost cost\n" +
				"\t\t\t\tfrom  v_adid_rebate_day b\n" +
				"\t\t\t\tleft JOIN ad_ptype ap\n" +
				"\t\t\t\ton ap.adid = b.ad_id\n" +
				"\t\t\t\tleft JOIN v_odsmysql_wan_game g\n" +
				"\t\t\t\ton ap.gameid=g.id\n" +
				"                LEFT JOIN odsmysql_wan_promotion_channel_v3 wpc  -- 渠道表\n" +
				"                ON wpc.isdelete = 0\n" +
				"                AND wpc.parent_code = ap.parentchl\n" +
				"                AND wpc.chncode = ap.chl\n" +
				"                LEFT JOIN odsmysql_ad_account aa ON\n" +
				"\t\t\t\t\taa.advertiser_id = b.ad_account\n" +
				"\t\t\t\tLEFT JOIN odsmysql_sys_user osu ON\n" +
				"\t\t\t\t\taa.throw_user = toString(osu.user_id) " +
				"\t\t\t\t\t left join odsmysql_sys_dept_group osdg\n" +
				"\t\t\t\t\t\t on osu.dept_group_id = osdg.id\n" +
				"                LEFT JOIN v_odsmysql_adid vad  -- 广告计划表\n" +
				"                ON b.ad_id = vad.adid\n" +
				"                AND b.ctype = vad.ctype\n" +
				"\t\t\t\tWHERE 1=1\n" +
				"\t\t\t\t--广告权限\n" +
				adAuthSql +
				"\tand  (cost + rudecost) > 0 \n" +
				"\t\t\t\tand b.date >= " + sTime +
				"\t\t\t\tand b.date <= " + eTime +
				"\t\t\t) a\n" +
				" WHERE 1=1 \n" +
				whereCondition +
				"\t\t\tgroup by " +
				"adid," +
				groupByCondition
		);
		query_sql.append(" ) t100 ");
		return query_sql;
	}


	//获取周期+类别：day/week/'汇总'/month,pgid,chl,os ; pgid,chl,os
	//周期不可为null
	//group
	private String getGroupByCondition(RetentionDto req){
		StringBuilder groupSql = new StringBuilder();
		String queryColumn = req.getQueryColumn();
		groupSql.append("period");
		//类别不为空时
		if (StringUtils.isNotBlank(queryColumn)){
			if(queryColumn.contains("userGroupId")){
				groupSql.append(",");
				groupSql.append("userGroupName");
			}
			groupSql.append(",");
			groupSql.append(queryColumn);
		}
		return groupSql.toString();
	}


	//select
	private String getSelectCondition(RetentionDto req){
		StringBuilder groupSql = new StringBuilder();
		String queryColumn = req.getQueryColumn();
		groupSql.append(" period ");
		//类别不为空时
		if (StringUtils.isNotBlank(queryColumn)){
			if(queryColumn.contains("userGroupId")){
				groupSql.append(",");
				groupSql.append("userGroupName");
			}
			groupSql.append(",");
			groupSql.append(queryColumn);
		}
		groupSql.append(",");
		return groupSql.toString();
	}


	//select
	private String getSelectConditionAndAlias(RetentionDto req,String alias){
		StringBuilder querySql = new StringBuilder();
		String queryColumn = req.getQueryColumn();
		querySql.append(alias+".period period, ");
		//类别不为空时
		if (StringUtils.isNotBlank(queryColumn)){
			String[] split = queryColumn.split(",");
			for (int i = 0; i < split.length; i++) {
				querySql.append(alias+"."+split[i]+ " " + split[i] );
				querySql.append(",");
				if(split[i].equals("userGroupId")){
					querySql.append(alias+".userGroupName userGroupName");
					querySql.append(",");
				}
			}
		}
		return querySql.toString();
	}


	//where 条件
	//筛选条件
	public String getWhereCondition(RetentionDto req, String bieming) {
		StringBuilder sqlCondition = new StringBuilder();
		//系统
		Integer os = req.getOs();
		//主游戏
		String pgidArr = req.getPgidArr();
		//主渠道
		String parentchlArr = req.getParentchlArr();
		//分包渠道
		String appchlArr = req.getAppchlArr();
		//部门
		String deptIdArr = req.getDeptIdArr();
		//组别
		String userGroupIdArr = req.getUserGroupIdArr();
		//投放人
		String investorArr = req.getInvestorArr();
		//子游戏
		String gameidArr = req.getGameidArr();
		//日期
		Long sTime = req.getSTime();
		Long eTime = req.getETime();
		if (StringUtils.isBlank(bieming)) {
			if (StringUtils.isNotBlank(deptIdArr)) {
				sqlCondition.append("       AND deptId IN (").append(deptIdArr).append(")");
			}
			if (StringUtils.isNotBlank(investorArr)) {
				sqlCondition.append("       AND investor IN (").append(investorArr).append(")");
			}
			if (StringUtils.isNotBlank(userGroupIdArr)) {
				sqlCondition.append(" AND userGroupId IN (").append(userGroupIdArr).append(")");
			}
			if (Objects.nonNull(sTime) && Objects.nonNull(eTime)) {
				sqlCondition.append(" and day  >= ").append(sTime);
				sqlCondition.append(" and day  <= ").append(eTime);
			}
			if (StringUtils.isNotBlank(parentchlArr)) {
				if (parentchlArr.contains(",")) {
					sqlCondition.append(" and parentchl in ('" + parentchlArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and parentchl  = '" + parentchlArr + "'");
				}
			}
			if (StringUtils.isNotBlank(appchlArr)) {
				if (appchlArr.contains(",")) {
					sqlCondition.append(" and appchl in ('" + appchlArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and appchl  = '" + appchlArr + "'");
				}
			}
			if (StringUtils.isNotBlank(pgidArr)) {
				if (pgidArr.contains(",")) {
					sqlCondition.append(" and pgid in (" + pgidArr + ")");
				} else {
					sqlCondition.append(" and pgid  = " + pgidArr);
				}
			}
			if (StringUtils.isNotBlank(gameidArr)) {
				if (gameidArr.contains(",")) {
					sqlCondition.append(" and  gameid in (" + gameidArr + ")");
				} else {
					sqlCondition.append(" and  gameid  = " + gameidArr);
				}
			}
			if (Objects.nonNull(os)) {
				sqlCondition.append(" and os  = ").append(os);
			}
			return sqlCondition.toString();
		} else {
			if (Objects.nonNull(sTime) && Objects.nonNull(eTime)) {
				sqlCondition.append(" and ").append(bieming).append(".day  >= ").append(sTime);
				sqlCondition.append(" and ").append(bieming).append(".day  <= ").append(eTime);
			}
			if (StringUtils.isNotBlank(deptIdArr)) {
				sqlCondition.append(" AND ").append(bieming).append(".deptId IN (").append(deptIdArr).append(")");
			}
			if (StringUtils.isNotBlank(investorArr)) {
				sqlCondition.append(" AND ").append(bieming).append(".investor IN (").append(investorArr).append(")");
			}
			if (StringUtils.isNotBlank(userGroupIdArr)) {
				sqlCondition.append(" AND ").append(bieming).append(".userGroupId IN (").append(userGroupIdArr).append(")");
			}
			if (StringUtils.isNotBlank(parentchlArr)) {
				if (parentchlArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".parentchl in ('" + parentchlArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".parentchl  = '" + parentchlArr + "'");
				}
			}
			if (StringUtils.isNotBlank(appchlArr)) {
				if (appchlArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".appchl in ('" + appchlArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".appchl  = '" + appchlArr + "'");
				}
			}
			if (StringUtils.isNotBlank(pgidArr)) {
				if (pgidArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".pgid in (" + pgidArr + ")");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".pgid  = " + pgidArr);
				}
			}
			if (StringUtils.isNotBlank(gameidArr)) {
				if (gameidArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".gameid in (" + gameidArr + ")");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".gameid  = " + gameidArr);
				}
			}
			if (Objects.nonNull(os)) {

				sqlCondition.append(" and ").append(bieming).append(".os  = " + os);
			}
			return sqlCondition.toString();
		}
	}


	//权限关联表
	private StringBuilder getAuthSql(RetentionDto req) {
		StringBuilder authSql = new StringBuilder();
		if (String.valueOf(req.getIsSys()) == null || req.getIsSys() != 1) {

			// -- 渠道权限
			authSql.append(" AND ( \n");
			authSql.append(" investor IN ( -- 管理的账号 \n");
			authSql.append(req.getUserIds());
			authSql.append(" ) \n");
			authSql.append("  )\n");
		}
		return authSql;
	}

	//广告权限
	private StringBuilder getAdAuthSql(RetentionDto req) {
		StringBuilder adAuthSql = new StringBuilder();
		if (String.valueOf(req.getIsSys()) == null || req.getIsSys() != 1) {
			// -- 广告权限
			adAuthSql.append("  AND b.ad_account IN (  \n");
			adAuthSql.append(req.getAdAccounts());
			adAuthSql.append("  ) \n");
		}
		return adAuthSql;
	}


	// 获取父渠道名称
	private StringBuilder getParentChlNameSql(RetentionDto req) {
		StringBuilder querySql = new StringBuilder();
		querySql.append(" ( SELECT chncode parentchl,chnname parentchlName FROM odsmysql_wan_promotion_channel_v3 where pid = 0 ) a ");
		return querySql;
	}


}
